/**
 * @Package: 	com.group.DAO 
 * @author: 	Liu  
 * @date: 		Dec 28, 2018 7:58:58 PM 
 */
package com.group.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.group.C3P0Utils.C3P0Utils;
import com.group.pojo.GClass;
import com.group.pojo.GEvaluate;
import com.group.pojo.GGroup;
import com.group.pojo.GStudent;
import com.group.pojo.GWork;

/**
 * @ClassName:	 GGroupDAO.java
 * @Description: 小组DAO
 * @author:		 Liu
 * @date: 		 Dec 28, 2018 7:58:58 PM 
 */
public class GGroupDAO {
	public List<GGroup> getGGroupinfo() {
		List<GGroup> group_list = new ArrayList<GGroup>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM g_group";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				GGroup g = new GGroup();
				g.setGroupID(rs.getInt("group_id"));
				g.setGroupName(rs.getString("group_name"));
				g.setStudentList(getGGroupMemberList(rs.getInt("group_id")));
				group_list.add(g);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
		return group_list;
	}
	
	/**
	 * @Function: 	 GGroupDAO.java
	 * @Description: 获取小组成员信息
	 * @param:		   参数
	 * @return:		   小组成员信息列表
	 * @author: 	 Liu
	 * @date:		 Dec 28, 2018 8:51:55 PM 
	 */
	public List<GStudent> getGGroupMemberList(int groupID){
		List<GStudent> studentList = new ArrayList<GStudent>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM g_student NATURAL JOIN g_class NATURAL JOIN g_group WHERE group_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, groupID);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				GStudent s = new GStudent();
				GClass c = new GClass(rs.getString("class_id"), rs.getString("class_name"), rs.getInt("class_year"));
				GGroup g = new GGroup(rs.getInt("group_id"), rs.getString("group_name"));
				s.setStudentID(rs.getString("student_id"));
				s.setStudentName(rs.getString("student_name"));
				s.setGgroup(g);
				s.setGclass(c);
				studentList.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
		return studentList;
	}
	
	
}
